<?php

namespace App\Exports;

use App\Models\OrderChild;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;

use Maatwebsite\Excel\Concerns\WithStyles;
//单元格格式化,单元格处理文本、数字、日期、金额等格式
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
//自动适应单元格宽
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;



class SalaryExport implements FromCollection, WithColumnFormatting, ShouldAutoSize,WithStyles
{
    /**订单导出**/
    private $params;
    /**
     * @var string[] 导出标题集合
     */
    private $headingList = [
        ['员工工资列表','','','','','',''],
        ['','','','','','',''],
        [
            'employee_name'=>'员工姓名',
            'start_time'=>'开始时间',
            'end_time'=>'结束时间',
            'order_count'=>'工单完成数',
            'total_sales'=>'工单总金额',
            'base_pay'=>'员工工资',
            'total'=>'合计'
        ]
    ];

    private $title='员工工资列表';



    public function __construct($params) {
        ob_end_clean();
        $this->params = $params;
    }

    //数组转集合
    public function collection()
    {
        return new Collection($this->createData());
    }

    //业务代码
    public function createData()
    {
        $data[] = $this->headingList;
        $params = $this->params;
        $array = OrderChild::Search($params)->select('employee_user_id','order_id', DB::raw('SUM(price) as total_sales'),DB::raw('count(*) as order_count'))
            ->whereNotNull('employee_user_id')
            ->where('is_del',2)
            ->with(['employee'=>function($q){
                $q->select('employee_name','base_pay','user_id');
            }])
            ->groupBy('employee_user_id')
            ->get()->toArray();
        foreach ($array as $key=>&$val){
            $data[] = [
                'employee_name'=>$val['employee']['employee_name'],
                'start_time'=>$params['start_time'],
                'end_time'=>$params['end_time'],
                'order_count'=>$val['order_count'],
                'total_sales'=>$val['total_sales'],
                'base_pay'=>$val['employee']['base_pay'],
                'total'=>$val['total_sales'] + $val['employee']['base_pay']
            ];
        }
        return $data;
    }


    public function columnFormats(): array
    {
        //'B' => NumberFormat::FORMAT_DATE_DDMMYYYY, //日期
        $format = NumberFormat::FORMAT_NUMBER_00;//金额保留两位小数
        return ['G' => $format, 'E' => $format,'F' => $format];

    }


    /**
     * sheet名称
     * @return string
     */
    public function title(): string
    {
        return $this->title;
    }

    /**
     * 设置列宽
     * @return array
     */
    public function columnWidths(): array
    {
        return [
            'A' => 100, 'B' => 100, 'C' => 100, 'D' => 100, 'E' => 100, 'F' => 100,'G' => 100
        ];
    }




    public function styles(Worksheet $sheet)
    {
        $sheet->getDefaultRowDimension()->setRowHeight(22);//设置行高
        $sheet->mergeCells('A1:G2'); //合并单元格
        $sheet->getStyle('A1:G2')
            ->applyFromArray([
                'font' => ['bold' => true],
                'color' => ['rgb' => '0072ff']
            ]);//字体设置
        // 设置背景色
        $sheet->getStyle('A1:G2')->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setRGB('e9e9e9');

        // setWrapText自动换行，setVertical垂直对齐方式
        $sheet->getStyle("A:G")->getAlignment()->setVertical('center');
        $sheet->getStyle('A:G')->applyFromArray(['alignment' => ['horizontal' => 'center']]);//设置水平居中


    }
}
